Convert MSSQL syntax to Oracle - variable declaration

[becksinthecity]

I'm trying to convert the below SQL into Oracle but am having some issues with the variable declarations.

SQL commands
declare @sqlstring varchar(500)
select @sqlstring= (select concat('ARC_ALD_',(select DATA_TABLE_ID from arc_active_list aal join arc_resource_ref arr on aal.ID=arr.ID where arr.uri like '%Lists/AccountLockouts' )) a )

exec('select * from'+@sqlstring+'')

End SQL


Basically the end result is a Select * from XXXXX where XXXXX is the variable value.


I would like to define a varchar2 variable SQLSTRING as the following statement

select concat('ARC_ALD_',(select DATA_TABLE_ID from arc_active_list aal join arc_resource_ref arr on aal.ID=arr.ID where arr.uri like '%Lists/AccountLockouts' )) a from dual

This returns a 5-10 varchar string which I then need to put into the select * from SQLSTRING statement which will return my results.

The issue i am running into is the embedded like statement and the apostrophes.

When I try and declare
sqlstring:=(select concat('ARC_ALD_',(select DATA_TABLE_ID from arc_active_list aal join arc_resource_ref arr on aal.ID=arr.ID where arr.uri like '%Lists/AccountLockouts' )) a from dual);

this gives me all sorts of syntax errors.

If i do (note extra ' 's)
sqlstring:='select concat('ARC_ALD_',(select DATA_TABLE_ID from arc_active_list aal join arc_resource_ref arr on aal.ID=arr.ID where arr.uri like '%Lists/AccountLockouts' )) a from dual';

then the like statement and ARC_ALD section fail.


Ideas?